package com.fuershiye.woman.monitor.dao;

import java.math.BigInteger;
import java.util.List;

import com.fuershiye.woman.monitor.beans.*;
import org.apache.commons.lang3.StringUtils;
import org.hibernate.Query;
import org.hibernate.SQLQuery;
import org.springframework.beans.factory.annotation.Autowired;
import org.springframework.stereotype.Repository;

import com.fuershiye.woman.monitor.model.Department;
import com.fuershiye.woman.monitor.utils.DaoSqlUtil;
import com.google.common.collect.Lists;

@Repository("departmentDao")
@SuppressWarnings("unchecked")
public class DepartmentDao {

    @Autowired
    private HibernateDao hibernateDao;

    public void save(Department department){
        hibernateDao.save(department);
    }

    @SuppressWarnings("unchecked")
	public List<Department> list(String name, Long type, Integer level, Integer status, Page page) {

    	StringBuffer sb = new StringBuffer("from Department where 1=1 ");

        sb = DaoSqlUtil.appendSql(sb, "type", type);
        sb = DaoSqlUtil.appendSql(sb, "level", level);
        sb = DaoSqlUtil.appendSql(sb, "status", status);
        if(StringUtils.isNotBlank(name)){
        	sb.append("and (name like:name or sname like:name or code like:name) ");
    	}
        
        Query query = hibernateDao.getQuery(sb.toString());

        query = DaoSqlUtil.appendQuery(query, "type", type);
        query = DaoSqlUtil.appendQuery(query, "level", level);
        query = DaoSqlUtil.appendQuery(query, "status", status);
        if(StringUtils.isNotBlank(name)){
            query.setString("name","%" + name + "%");
    	}
    	if(page!=null){
            query.setFirstResult(page.getStart());
            query.setMaxResults(page.getPageSize());
        }
    	
        return query.list();
    }
    
    public int getTotal(String name, Long type, Integer level, Integer status) {
    	
    	StringBuffer sb = new StringBuffer("select count(*) from department where 1=1 ");

        sb = DaoSqlUtil.appendSql(sb, "type", type);
        sb = DaoSqlUtil.appendSql(sb, "level", level);
        sb = DaoSqlUtil.appendSql(sb, "status", status);
    	if(StringUtils.isNotBlank(name)){
    		sb.append("and (name like:name or sname like:name or code like:name) ");
    	}
    	
    	Query query = hibernateDao.getSqlQuery(sb.toString());

        query = DaoSqlUtil.appendQuery(query, "type", type);
        query = DaoSqlUtil.appendQuery(query, "level", level);
        query = DaoSqlUtil.appendQuery(query, "status", status);
    	if(StringUtils.isNotBlank(name)){
    		query.setString("name","%" + name + "%");
    	}

        BigInteger count = (BigInteger) query.uniqueResult();
        return (int) count.longValue();
    }
    
    @SuppressWarnings("unchecked")
    public List<IdNameArea> listByPidOrArea(Long pid, Long area) {

        List<IdNameArea> result = Lists.newArrayList();

    	StringBuffer sb = new StringBuffer("select id,name,area from department where 1=1 ");
    	

        sb = DaoSqlUtil.appendSql(sb, "pid", pid);
        sb = DaoSqlUtil.appendSql(sb, "area", area);
        
        Query query = hibernateDao.getSqlQuery(sb.toString());

        query = DaoSqlUtil.appendQuery(query, "pid", pid);
        query = DaoSqlUtil.appendQuery(query, "area", area);
        
        List<Object[]> queryResult = query.list();

//    	IdNameArea idNameAreaAll = new IdNameArea();
//    	idNameAreaAll.setId(0L);
//    	idNameAreaAll.setArea(0L);
//    	idNameAreaAll.setName("全部");
//    	result.add(idNameAreaAll);
    	
        for (Object[] fields : queryResult) {
        	IdNameArea idNameArea = new IdNameArea();
        	idNameArea.setId(fields[0] == null? 0:Long.parseLong(fields[0].toString()));
        	idNameArea.setName(fields[1] == null? "":fields[1].toString());
        	idNameArea.setArea(fields[2] == null? 0:Long.parseLong(fields[2].toString()));
            result.add(idNameArea);
		}
	  	return result;
    }

    public Department get(Long id) {
        return hibernateDao.getById(Department.class,id);
    }

    public Department findByUserName(String userName) {

        String sql = "select * from department d " +
                "where d.id=(select department from admin_role r where r.id=(select rid from admin_user_role ur where ur.uid=(select id from admin_user u where u.user_name=?)))";
        SQLQuery query = hibernateDao.getSqlQuery(sql).addEntity(Department.class);
        query.setString(0, userName);
        return (Department)query.uniqueResult();
    }
    
    @SuppressWarnings("unchecked")
	public Department findByAreaAndType(Long area,Long type) {
    	
    	StringBuffer sb = new StringBuffer("from Department where 1=1 ");

        sb = DaoSqlUtil.appendSql(sb, "area", area);
        sb = DaoSqlUtil.appendSql(sb, "type", type);
        sb = DaoSqlUtil.appendSql(sb, "status", 1);
    	
    	Query query = hibernateDao.getQuery(sb.toString());

        query = DaoSqlUtil.appendQuery(query, "area", area);
        query = DaoSqlUtil.appendQuery(query, "type", type);
        query = DaoSqlUtil.appendQuery(query, "status", 1);

        List<Department> result = query.list();
        if(result==null||result.size()<1){
        	return null;
        }
        return result.get(0);
    
    }

    public void delete(Long id) {
        String sql = "update Department set status = 0 where id = ?";
        SQLQuery query = hibernateDao.getSqlQuery(sql).addEntity(Department.class);
        query.setLong(0, id);
        query.executeUpdate();
    }

    public List<Long> getIdsByCondition(Long area, Integer level, Long pid, Long type, int status) {

        StringBuffer sb = new StringBuffer("select id from department where 1=1 ");

        sb = DaoSqlUtil.appendSql(sb, "area", area);
        sb = DaoSqlUtil.appendSql(sb, "level", level);
        sb = DaoSqlUtil.appendSql(sb, "pid", pid);
        sb = DaoSqlUtil.appendSql(sb, "type", type);
        sb = DaoSqlUtil.appendSql(sb, "status", status);

        sb.append("ORDER BY id DESC");
        Query query = hibernateDao.getSqlQuery(sb.toString());
        query = DaoSqlUtil.appendQuery(query, "area", area);
        query = DaoSqlUtil.appendQuery(query, "level", level);
        query = DaoSqlUtil.appendQuery(query, "pid", pid);
        query = DaoSqlUtil.appendQuery(query, "type", type);
        query = DaoSqlUtil.appendQuery(query, "status", status);

        return query.list();
    }
    @SuppressWarnings("unchecked")
    public List<IdNameSname> getAllSnames(Integer level, Long type, Integer status) {

        List<IdNameSname> result = Lists.newArrayList();

        StringBuffer sb = new StringBuffer("select id,name,sname from department where 1=1 ");

        sb = DaoSqlUtil.appendSql(sb, "level", level);
        sb = DaoSqlUtil.appendSql(sb, "type", type);
        sb = DaoSqlUtil.appendSql(sb, "status", status);
        
        sb.append("ORDER BY id DESC");
        Query query = hibernateDao.getSqlQuery(sb.toString());
        query = DaoSqlUtil.appendQuery(query, "level", level);
        query = DaoSqlUtil.appendQuery(query, "type", type);
        query = DaoSqlUtil.appendQuery(query, "status", status);
        
        List<Object[]> queryResult = query.list();
        
        for (Object[] fields : queryResult) {
        	IdNameSname idNameSname = new IdNameSname();
        	idNameSname.setId(fields[0] == null? 0:Long.parseLong(fields[0].toString()));
        	idNameSname.setName(fields[1] == null? "":fields[1].toString());
        	idNameSname.setSname(fields[2] == null? "":fields[2].toString());
    	  	result.add(idNameSname);
		}
	  	return result;
    }

    @SuppressWarnings("unchecked")
    public List<IdAreaId> getAllIdAreaId(Integer level, Long type) {

        List<IdAreaId> result = Lists.newArrayList();

        StringBuffer sb = new StringBuffer("select id,area from department where 1=1 ");

        sb = DaoSqlUtil.appendSql(sb, "level", level);
        sb = DaoSqlUtil.appendSql(sb, "type", type);

        sb.append("ORDER BY id DESC");
        Query query = hibernateDao.getSqlQuery(sb.toString());
        query = DaoSqlUtil.appendQuery(query, "level", level);
        query = DaoSqlUtil.appendQuery(query, "type", type);

        List<Object[]> queryResult = query.list();

        for (Object[] fields : queryResult) {
            IdAreaId idAreaId = new IdAreaId();
            idAreaId.setId(fields[0] == null? 0:Long.parseLong(fields[0].toString()));
            idAreaId.setAreaid(fields[1] == null? 0:Long.parseLong(fields[1].toString()));
            result.add(idAreaId);
        }
        return result;
    }

    @SuppressWarnings("unchecked")
    public List<IdNameSname> getAllByLevelAndArea(Integer level, Long area, int status) {
    	
    	List<IdNameSname> result = Lists.newArrayList();
    	
    	StringBuffer sb = new StringBuffer("select id,name,sname from department where 1=1 ");
    	
    	sb = DaoSqlUtil.appendSql(sb, "level", level);
    	sb = DaoSqlUtil.appendSql(sb, "area", area);
    	sb = DaoSqlUtil.appendSql(sb, "status", status);
    	
    	sb.append("ORDER BY code ASC");
    	Query query = hibernateDao.getSqlQuery(sb.toString());
    	query = DaoSqlUtil.appendQuery(query, "level", level);
    	query = DaoSqlUtil.appendQuery(query, "area", area);
    	query = DaoSqlUtil.appendQuery(query, "status", status);
    	
    	List<Object[]> queryResult = query.list();
    	
    	for (Object[] fields : queryResult) {
    		IdNameSname idNameSname = new IdNameSname();
    		idNameSname.setId(fields[0] == null? 0:Long.parseLong(fields[0].toString()));
    		idNameSname.setName(fields[1] == null? "":fields[1].toString());
    		idNameSname.setSname(fields[2] == null? "":fields[2].toString());
    		result.add(idNameSname);
    	}
    	return result;
    }
    
    
}
